In [21]:
def create_download_link(df=pd.DataFrame, title = "Click to Download", filename=str, level='GP'):
if level=='Village':
csv = df.to_csv(index=True,index_label=['District','Janpad','Gram Panchayat','Village'])
elif level=='GP':
csv = df.to_csv(index=True,index_label=['District','Janpad','Gram Panchayat'])
elif level=='Janpad':
csv = df.to_csv(index=True,index_label=['District','Janpad'])
elif level=='District':
csv = df.to_csv(index=True,index_label=['District'])
else:
csv = df.to_csv(index=True,index_label=['District','Janpad','Gram Panchayat'])
b64 = base64.b64encode(csv.encode())
payload = b64.decode()
html = '<a download="{filename}" href="data:text/csv;base64,{payload}" target="_blank">{title}</a>'
###html = '<input type="button" value="Download" download="{filename}" onclick="data:text/csv;base64,{payload}" target="_blank">{title}</input>'
html = html.format(payload=payload,title=title,filename=filename)
return HTML(html)
Section 1: Information about the file -¶
- Total records
Out[22]:
- Number of Columns (Data Columns)
Out[23]:
- Column Names and Data Type
Out[24]:
Section 2: Snapshot of the data -¶
Does the data contain any empty cell?¶
True - Yes
False - NoIn [26]:
raw_data.empty
Out[26]:
Identify duplicate records¶
Based on the combination - District+Janpad+Gram Panchayat+Village+Name of head of household+GOI ID
In [27]:
raw_data["is_duplicate_record"] = raw_data.duplicated(subset=("District",
"Janpad",
"Gram Panchayat",
"Village",
"Name of head of household",
"GOI ID"),
keep='last')
Section 3: Data Error Checks¶
1. Total Duplicate records:¶
Out[28]:
Deduplicating the data
Number of unique Records -
Out[29]:
2. Missing Data in Mandatory Columns:¶
Observations:¶
a. All the values in Column_1 are missing. b. There is no other missing value error.
3. Number of records where toilet is available in the household but type of toilet has either not been provided or mentioned as not available -¶
In [31]:
"""Data Error Condition 3: Number of Records where toilet is available but type of toilet has been mentioned उपलब्ध नहीं है"""
total_hh=np.sum(raw_data['Total Household'])
raw_data_tlt_nttype = raw_data[(raw_data["Has Toilet"] == 'हाँ') & (raw_data["Type of toilet"] == 'उपलब्ध नहीं है')]
x=raw_data_tlt_nttype.shape[0]
y=round(x/total_hh*100,2)
print("No of Records:",x," (",y,"%)")
4. Number of records where toilet is not available in the household but type of toilet has been mentioned -¶
In [32]:
"""Data Error Condition 4: Number of Records where toilet is not available but type of toilet has been mentioned other than उपलब्ध नहीं है"""
raw_data_ntlt_ttype=raw_data[(raw_data["Has Toilet"] == 'नहीं') & (raw_data["Type of toilet"] != 'उपलब्ध नहीं है')]
x=raw_data_ntlt_ttype.shape[0]
y=round(x/total_hh*100,2)
print("No of Records:",x," (",y,"%)")
5. Number of records where water source is available in the household but type of source has either not been provided or mentioned as not available -¶
In [33]:
"""Data Error Condition 3: Number of Records where water source is available but type of source has been mentioned as not available"""
raw_data_ws_nwstype=raw_data[(raw_data["Has Water Source"] == 'हाँ') & ((raw_data["Water source"] == 'उपलब्ध नहीं है') | (raw_data["Water source"] == 'पानी का कोई स्त्रोत नहीं'))]
x=raw_data_ws_nwstype.shape[0]
y=round(x/total_hh*100,2)
print("No of Records:",x," (",y,"%)")
6. Number of records where water source is not available in the household but type of source has been provided -¶
In [34]:
"""Data Error Condition 3: Number of Records where water source is not available but type of source has been mentioned as available"""
raw_data_nws_wstype=raw_data[(raw_data["Has Water Source"] == 'नहीं') & (raw_data["Water source"] != 'उपलब्ध नहीं है') & (raw_data["Water source"] != 'पानी का कोई स्त्रोत नहीं')]
x=raw_data_nws_wstype.shape[0]
y=round(x/total_hh*100,2)
print("No of Records:",x," (",y,"%)")
Total Households Surveyed in Verification:¶
Out[35]:
Households by Economic Status¶
Observations:¶
- Distribution of households by economic status is quite even, i.e. there are almost 50% BPL and 50% APL families where verification of toilet has been conducted.
Households by Social Category¶
Households by Economic Status and Social Category¶
Observations -¶
- There are 22.7% households in "Others" category
- All the General category households are BPL
- BPL Households has only General, SC and ST category. No landless family, or household headed by a woman or physically challenged person has been reported as BPL.
Observations:¶
- 94.85% of the housheolds have toilet
- However 4.55 lakh households are still devoid of toilet facility. ST households are the most affected
Availabilty of Different Types of Toilet across Households¶
In [47]:
print("Number of households having different types toilet")
print("--------------------------------------------------")
hh_hvng_tlt_type_cnt = hh_with_toilet["Type of toilet"].apply(lambda x: "Single Pit" if x=="एक गड्ढे वाला" else ("Double Pit" if x=="दो गड्ढे वाला" else ("Septic Tank" if x=="सेप्टिक टैंक" else ("Septic Tank with Soak Pit" if x=="सोखते गड्ढे के साथ सेप्टिक टैंक" else ("Bio Toilet" if x== "बायो-शौचालय" else "Not Available"))))).value_counts()
print(hh_hvng_tlt_type_cnt)
print("--------------------------------------------------")
In [48]:
display_donut(hh_with_toilet["Type of toilet"].apply(lambda x: "Single Pit" if x=="एक गड्ढे वाला" else ("Double Pit" if x=="दो गड्ढे वाला" else ("Septic Tank" if x=="सेप्टिक टैंक" else ("Septic Tank with Soak Pit" if x=="सोखते गड्ढे के साथ सेप्टिक टैंक" else ("Bio Toilet" if x== "बायो-शौचालय" else "Not Available"))))),
width=5,
height=5,
title="% of Households having different Types of Toilets",
pct=True)
In [49]:
print("Number of households having water source")
print("-----------------------")
display(raw_data["Has Water Source"].apply(lambda x: "Yes" if x=="हाँ" else "No").value_counts())
In [50]:
display_donut(raw_data["Has Water Source"].apply(lambda x: "Yes" if x=="हाँ" else "No"),
width=5,
height=5,
title="Availability of Water Source in Households",
pct=True)
Availability of water source in households across different economic status
Availability of water source in households across different social strata
Observation:¶
- 88.77% of the households have a water source
- Howerver, around 9.9 Lakh Households don't have water source. ST category households are the worst affected.
Availabilty of Different Types of Water Sources across Households¶
Availability of Toilets and Water Source in the Households¶
Observation:¶
- Handpump is the major source of water in households (55% of households use handpumps)
- Water Supply scheme is available in only 29.7% ~ 30% of the households and the remaining.
- 14% of the households rely on wells
- Around 5.56 Lakh households having toilets has no water source
Section 3: Status of toilets across State and Districts¶
Out[58]:
Observations¶
- In Jabalpur 23.27% of the households do not have toilet, followed by Seoni with 15.1% such households
- Again, Jabalpur and Seoni are the two districts with highest % of households without any source of water (with 33.75% and 27.78% respectively)
- Anuppur and Mandala districts have the highest proportion of households having a toilet but no water source (with 18.37% and 16.2% respectively)
Section 4: List of Distribution of Households¶
Number of duplicate records region wise
In [59]:
dup_rec=raw_data_dup.pivot_table(index=["District","Janpad","Gram Panchayat"],
values='Total Household',
aggfunc=np.sum).sort_values('Total Household', ascending=False)
create_download_link(dup_rec,filename="Household Verification - Duplicate Surveys by region.csv")
Out[59]:
Status of toilets across State and Districts
In [60]:
hh_tnu_tlt_status = raw_data.pivot_table(index=['District','Janpad','Gram Panchayat'],
values=['Total Household',
'% Headed by Women',
'% Headed by PWD',
'% Having No Toilet',
'% Having No Water Source',
'% Households having Toilet but no Water Source'],
aggfunc={'Total Household':np.sum,
'% Headed by Women':np.mean,
'% Headed by PWD':np.mean,
'% Having No Toilet':np.mean,
'% Having No Water Source':np.mean,
'% Households having Toilet but no Water Source':np.mean},
margins=True).apply(lambda x: round(x,2))
create_download_link(hh_tnu_tlt_status, filename="Household Verification - Status of Toilets in the region.csv")
Out[60]:
Number of Households across different socio-economic categories
In [61]:
hh_by_scio_eco=raw_data.pivot_table(index=["District","Janpad", "Gram Panchayat"],
columns=["Economic status", "Sub-category"],
values=['Total Household'],
aggfunc={'Total Household':np.sum},
margins=True)
create_download_link(hh_by_scio_eco,filename="Household Verification - Distribution of Households by Socio Economic Status.csv")
Out[61]:
List of Households Not Having Toilet
In [62]:
hh_no_tlt=raw_data[raw_data['Has Toilet']!="हाँ"].pivot_table(index=["District",
"Janpad",
"Gram Panchayat",
"Village"],
values='Total Household',
aggfunc=np.sum).sort_values('Total Household', ascending=False)
create_download_link(hh_no_tlt,filename="Household Verification - Households not having toilet.csv",level="Village")
Out[62]:
Number of records where toilet is available in the household but type of toilet has either not been provided or mentioned as not available
In [63]:
raw_data_tlt_nttype_pv=raw_data_tlt_nttype.pivot_table(index=["District","Janpad","Gram Panchayat"],
values='Total Household',
aggfunc=np.sum).sort_values('Total Household', ascending=False)
create_download_link(raw_data_tlt_nttype_pv,filename="Household Verification Error - Missing/Incorect Type of Toilet.csv")
Out[63]:
Number of records where toilet is not available in the household but type of toilet has been mentioned
In [64]:
raw_data_ntlt_ttype_pv=raw_data_ntlt_ttype.pivot_table(index=["District","Janpad","Gram Panchayat"],
values='Total Household',
aggfunc=np.sum).sort_values('Total Household', ascending=False)
create_download_link(raw_data_ntlt_ttype_pv,filename="Household Verification Error - No Toilet but Type Mentioned.csv")
Out[64]:
Number of records where water source is available in the household but type of source has either not been provided or mentioned as not available
In [65]:
raw_data_ws_nwstype_pv=raw_data_ws_nwstype.pivot_table(index=["District","Janpad","Gram Panchayat"],
values='Total Household',
aggfunc=np.sum).sort_values('Total Household', ascending=False)
create_download_link(raw_data_ws_nwstype_pv,filename="Household Verification Error - Water Source Missing.csv")
Out[65]:
Number of records where water source is not available in the household but type of source has been provided
In [66]:
raw_data_nws_wstype_pv=raw_data_nws_wstype.pivot_table(index=["District","Janpad","Gram Panchayat"],
values='Total Household',
aggfunc=np.sum).sort_values('Total Household', ascending=False)
create_download_link(raw_data_nws_wstype_pv,filename="Household Verification Error - No Water but Source Provided.csv")
Out[66]: